#!/bin/bash

计算数据库中 每张表的大小

SELECT 
	TABLE_SCHEMA as 库名称,
	TABLE_NAME as 表名称,
	concat((DATA_LENGTH/1024/1024),' MB') as 数据大小MB ,
	concat((INDEX_LENGTH/1024/1024),' MB') as 索引大小MB, 
	((DATA_LENGTH+INDEX_LENGTH)/1024/1024) as AllMB,
	TABLE_ROWS as 表中几行数据
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA not in ('performance_schema','mysql','information_schema');


SELECT 
   TABLE_NAME,
	(DATA_LENGTH/1024/1024) as DataM ,
	(INDEX_LENGTH/1024/1024) as IndexM, 
	((DATA_LENGTH+INDEX_LENGTH)/1024/1024) as AllM,
	TABLE_ROWS
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA = 'db_name';

SELECT 
	TABLE_SCHEMA,
	TABLE_NAME,
	(DATA_LENGTH/1024/1024) as DataMB ,
	(INDEX_LENGTH/1024/1024) as IndexMB, 
	((DATA_LENGTH+INDEX_LENGTH)/1024/1024) as AllMB,
	TABLE_ROWS
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA in ('db_name1','db_name2','db_name3','db_name3','db_name4');

SELECT 
	TABLE_SCHEMA,
	TABLE_NAME,
	(DATA_LENGTH/1024/1024) as DataMB ,
	(INDEX_LENGTH/1024/1024) as IndexMB, 
	((DATA_LENGTH+INDEX_LENGTH)/1024/1024) as AllMB,
	TABLE_ROWS
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA not in ('performance_schema','mysql','information_schema');


有时候需要查询MySQL数据库中各个表大小，该如何操作呢？
MySQL中有一个名为 information_schema 的数据库，在该库中有一个 TABLES 表，这个表主要字段分别是：
	TABLE_SCHEMA : 数据库名
	TABLE_NAME：表名
	ENGINE：所使用的存储引擎
	TABLES_ROWS：记录数
	DATA_LENGTH：数据大小
	INDEX_LENGTH：索引大小

其他字段请参考MySQL的手册。

统计各个库的大小
	select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
	concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
	from information_schema.tables
	group by TABLE_SCHEMA
	order by data_length desc;


	select TABLE_SCHEMA,TABLE_NAME, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
	concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
	from information_schema.tables
	group by TABLE_SCHEMA,TABLE_NAME
	order by TABLE_SCHEMA desc,TABLE_NAME desc ,data_length desc;

# mysql 查询库名，表名，主键名
select table_schema, table_name,column_name from  INFORMATION_SCHEMA.KEY_COLUMN_USAGE  t where t.table_schema='db_name'
select table_schema, table_name,column_name from  INFORMATION_SCHEMA.KEY_COLUMN_USAGE  t 
select table_schema, table_name,column_name from  INFORMATION_SCHEMA.KEY_COLUMN_USAGE  t where t.table_schema not in  ('performance_schema','mysql','information_schema');
# mysql -h${ip_host} -u${user_name} -p${user_password}  -e  " select table_schema,table_name,column_name  from  INFORMATION_SCHEMA.KEY_COLUMN_USAGE  t where t.table_schema not in  ('performance_schema','mysql','information_schema') group by table_name order by t.table_schema desc,table_name desc ; "  > /tmp/db-table.sql


